﻿// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Data;
using System.Data.Common;
using System.Text;
using Kailua.net.windward.utils;
using net.windward.utils.ado.OleDb;

namespace net.windward.utils.ado.Excel
{
	/// <summary>
	/// Wraps a DbCommand object and provides a client independent method of setting parameters. All parameters are
	/// set as ? in the sql commands are are set based on their order. This also has an ExecuteInsert that if given
	/// a sql command to insert a single row, will return the value of the auto primary key for that row.
	/// </summary>
	public class WrExcelCommand : WrCommand
	{
        private readonly DbProviderFactory provider;
        private readonly IWrSyntax syntax;

		/// <summary>
		/// Create a DbCommand wrapper.
		/// </summary>
		/// <param name="provider">The provider for this command.</param>
		/// <param name="odbcProvider">The ODBC provider for the connection this command is for. Can be null.</param>
		public WrExcelCommand(DbProviderFactory provider, string odbcProvider)
		{
            this.provider = provider;
            syntax = new WrExcelSyntax(provider, odbcProvider);
            cmd = provider.CreateCommand();
            cmd.CommandType = CommandType.Text;
		}

        /// <summary>
        /// Set the value of a parameter.
        /// </summary>
        /// <param name="ind">The index of this paramater in the command.</param>
        /// <param name="value">The value of this parameter.</param>
        public override void Set(int ind, object value)
        {

            if (value == null)
                value = DBNull.Value;

            DbParameter param = provider.CreateParameter();
            param.ParameterName = "?p" + ind;
            param.Value = value;

            cmd.Parameters.Add(param);
        }

        ///<summary>
        ///Used for inserting a row with an auto primary key. Executes an SQL statement against the Connection object of a .NET Framework 
        /// data provider, and returns the value of the primary key.
        ///</summary>
        /// <param name="pkName">The name of the primary key being automatically set.</param>
        ///<returns>
        ///The value of the primary key for the inserted row.
        ///</returns>
        ///<exception cref="T:System.InvalidOperationException">The connection does not exist -or- The connection is not open. </exception><filterpriority>2</filterpriority>
        public override long ExecuteInsert(string pkName)
        {
            cmd.CommandText += "; select LAST_INSERT_ID()";
            object rtn = cmd.ExecuteScalar();
            return (long)rtn;
        }

        #region IDbCommand overrides

        ///<summary>
        ///Gets or sets the text command to run against the data source.
        ///</summary>
        ///<returns>
        ///The text command to execute. The default value is an empty string ("").
        ///</returns>
        ///<filterpriority>2</filterpriority>
        public override string CommandText
        {
            get
            {
                return cmd.CommandText;
            }
            set
            {
                cmd.CommandText = syntax.ConvertSelect(SubCommands(value));
            }
        }

        private static string SubCommands(string cmd)
        {

            StringBuilder buf = new StringBuilder();
            int param = 0;
            foreach (char ch in cmd)
                if (ch != '?')
                    buf.Append(ch);
                else
                    buf.Append("?p" + param++);
            return buf.ToString().Trim();
        }

        ///<summary>
        ///Executes the <see cref="P:System.Data.IDbCommand.CommandText"></see> against the 
        /// <see cref="P:System.Data.IDbCommand.Connection"></see> and builds an <see cref="T:System.Data.IDataReader"></see>.
        ///</summary>
        ///
        ///<returns>
        ///An <see cref="T:System.Data.IDataReader"></see> object.
        ///</returns>
        ///<filterpriority>2</filterpriority>
        public override IDataReader ExecuteReader()
        {
            return new WrExcelDataReader(cmd.ExecuteReader());
        }

        ///<summary>
        ///Executes the <see cref="P:System.Data.IDbCommand.CommandText"></see> against the 
        /// <see cref="P:System.Data.IDbCommand.Connection"></see>, and builds an <see cref="T:System.Data.IDataReader"></see> 
        /// using one of the <see cref="T:System.Data.CommandBehavior"></see> values.
        ///</summary>
        ///
        ///<returns>
        ///An <see cref="T:System.Data.IDataReader"></see> object.
        ///</returns>
        ///
        ///<param name="behavior">One of the <see cref="T:System.Data.CommandBehavior"></see> values. </param>
        /// <filterpriority>2</filterpriority>
        public override IDataReader ExecuteReader(CommandBehavior behavior)
        {
            Trap.trap();
            return new WrExcelDataReader(cmd.ExecuteReader(behavior));
        }

        #endregion

	}
}
